/*
Navicat SQL Server Data Transfer

Source Server         : 192.168.1.111
Source Server Version : 100000
Source Host           : 192.168.1.111:1433
Source Database       : docmgr
Source Schema         : dbo

Target Server Type    : SQL Server
Target Server Version : 100000
File Encoding         : 65001

Date: 2015-12-04 14:20:23
*/


-- ----------------------------
-- Table structure for Authority
-- ----------------------------
DROP TABLE [dbo].[Authority]
GO
CREATE TABLE [dbo].[Authority] (
[ID] int NOT NULL IDENTITY(1,1) ,
[Code] varchar(50) NOT NULL ,
[Name] varchar(100) NOT NULL ,
[Remark] varchar(400) NULL 
)


GO
DBCC CHECKIDENT(N'[dbo].[Authority]', RESEED, 10)
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'Authority', 
NULL, NULL)) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'权限'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Authority'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'权限'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Authority'
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'Authority', 
'COLUMN', N'ID')) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'ID'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Authority'
, @level2type = 'COLUMN', @level2name = N'ID'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'ID'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Authority'
, @level2type = 'COLUMN', @level2name = N'ID'
GO

-- ----------------------------
-- Table structure for Department
-- ----------------------------
DROP TABLE [dbo].[Department]
GO
CREATE TABLE [dbo].[Department] (
[ID] int NOT NULL IDENTITY(1,1) ,
[Name] varchar(100) NOT NULL ,
[Remark] varchar(100) NULL 
)


GO
DBCC CHECKIDENT(N'[dbo].[Department]', RESEED, 7)
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'Department', 
NULL, NULL)) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'部门'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Department'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'部门'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Department'
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'Department', 
'COLUMN', N'ID')) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'ID'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Department'
, @level2type = 'COLUMN', @level2name = N'ID'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'ID'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Department'
, @level2type = 'COLUMN', @level2name = N'ID'
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'Department', 
'COLUMN', N'Name')) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'部门名称'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Department'
, @level2type = 'COLUMN', @level2name = N'Name'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'部门名称'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Department'
, @level2type = 'COLUMN', @level2name = N'Name'
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'Department', 
'COLUMN', N'Remark')) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'备注'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Department'
, @level2type = 'COLUMN', @level2name = N'Remark'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'备注'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Department'
, @level2type = 'COLUMN', @level2name = N'Remark'
GO

-- ----------------------------
-- Table structure for Directory
-- ----------------------------
DROP TABLE [dbo].[Directory]
GO
CREATE TABLE [dbo].[Directory] (
[ID] int NOT NULL IDENTITY(1,1) ,
[Type] nchar(10) NOT NULL ,
[Name] nchar(10) NOT NULL 
)


GO
DBCC CHECKIDENT(N'[dbo].[Directory]', RESEED, 20)
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'Directory', 
'COLUMN', N'Type')) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'类型'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Directory'
, @level2type = 'COLUMN', @level2name = N'Type'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'类型'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Directory'
, @level2type = 'COLUMN', @level2name = N'Type'
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'Directory', 
'COLUMN', N'Name')) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'名称'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Directory'
, @level2type = 'COLUMN', @level2name = N'Name'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'名称'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Directory'
, @level2type = 'COLUMN', @level2name = N'Name'
GO

-- ----------------------------
-- Table structure for DocConcerned
-- ----------------------------
DROP TABLE [dbo].[DocConcerned]
GO
CREATE TABLE [dbo].[DocConcerned] (
[ID] int NOT NULL IDENTITY(1,1) ,
[UserID] int NULL ,
[DocID] int NULL 
)


GO
DBCC CHECKIDENT(N'[dbo].[DocConcerned]', RESEED, 15)
GO

-- ----------------------------
-- Table structure for DocHandler
-- ----------------------------
DROP TABLE [dbo].[DocHandler]
GO
CREATE TABLE [dbo].[DocHandler] (
[DocID] int NULL ,
[No] int NULL ,
[HandlerUserID] int NULL ,
[ID] int NOT NULL IDENTITY(1,1) ,
[Status] varchar(10) NULL ,
[Code] varchar(20) NOT NULL ,
[SysSerialNumber] varchar(30) NULL 
)


GO
DBCC CHECKIDENT(N'[dbo].[DocHandler]', RESEED, 139)
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'DocHandler', 
'COLUMN', N'Status')) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'每份文件的状态'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'DocHandler'
, @level2type = 'COLUMN', @level2name = N'Status'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'每份文件的状态'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'DocHandler'
, @level2type = 'COLUMN', @level2name = N'Status'
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'DocHandler', 
'COLUMN', N'Code')) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'唯一字段就酱紫。啦啦啦'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'DocHandler'
, @level2type = 'COLUMN', @level2name = N'Code'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'唯一字段就酱紫。啦啦啦'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'DocHandler'
, @level2type = 'COLUMN', @level2name = N'Code'
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'DocHandler', 
'COLUMN', N'SysSerialNumber')) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'卷号'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'DocHandler'
, @level2type = 'COLUMN', @level2name = N'SysSerialNumber'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'卷号'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'DocHandler'
, @level2type = 'COLUMN', @level2name = N'SysSerialNumber'
GO

-- ----------------------------
-- Table structure for DocReadLog
-- ----------------------------
DROP TABLE [dbo].[DocReadLog]
GO
CREATE TABLE [dbo].[DocReadLog] (
[ID] int NOT NULL IDENTITY(1,1) ,
[DocumentID] int NULL ,
[LeaderId] int NULL ,
[StartDate] smalldatetime NULL ,
[Status] varchar(20) NULL ,
[ReadDate] smalldatetime NULL ,
[ReadUserID] int NULL ,
[Comments] varchar(500) NULL 
)


GO
DBCC CHECKIDENT(N'[dbo].[DocReadLog]', RESEED, 227)
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'DocReadLog', 
NULL, NULL)) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'文档记录'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'DocReadLog'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'文档记录'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'DocReadLog'
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'DocReadLog', 
'COLUMN', N'LeaderId')) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'发起人'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'DocReadLog'
, @level2type = 'COLUMN', @level2name = N'LeaderId'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'发起人'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'DocReadLog'
, @level2type = 'COLUMN', @level2name = N'LeaderId'
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'DocReadLog', 
'COLUMN', N'Comments')) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'批阅意见'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'DocReadLog'
, @level2type = 'COLUMN', @level2name = N'Comments'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'批阅意见'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'DocReadLog'
, @level2type = 'COLUMN', @level2name = N'Comments'
GO

-- ----------------------------
-- Table structure for Document
-- ----------------------------
DROP TABLE [dbo].[Document]
GO
CREATE TABLE [dbo].[Document] (
[ID] int NOT NULL IDENTITY(1,1) ,
[DocName] varchar(100) NULL ,
[Remark] varchar(100) NULL ,
[RegDate] smalldatetime NOT NULL ,
[UserID] int NOT NULL ,
[SysSerialNumber] varchar(20) NULL ,
[DocSerialNumber] varchar(100) NULL ,
[SecretLevel] varchar(10) NULL ,
[DepartmentID] int NULL ,
[DocType] varchar(10) NULL ,
[PageNumber] int NULL ,
[Status] varchar(20) NULL ,
[Code] varchar(100) NULL ,
[HandleUserID] int NULL ,
[OutSideDepartMent] varchar(100) NULL ,
[Portion] int NULL DEFAULT ((1)) ,
[DocClassID] int NULL 
)


GO
DBCC CHECKIDENT(N'[dbo].[Document]', RESEED, 171)
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'Document', 
'COLUMN', N'DocName')) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'可以为空的。'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Document'
, @level2type = 'COLUMN', @level2name = N'DocName'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'可以为空的。'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Document'
, @level2type = 'COLUMN', @level2name = N'DocName'
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'Document', 
'COLUMN', N'UserID')) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'签收人 或者是 处理人'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Document'
, @level2type = 'COLUMN', @level2name = N'UserID'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'签收人 或者是 处理人'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Document'
, @level2type = 'COLUMN', @level2name = N'UserID'
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'Document', 
'COLUMN', N'SysSerialNumber')) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'卷号 这个 终于被用了起来，哈哈'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Document'
, @level2type = 'COLUMN', @level2name = N'SysSerialNumber'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'卷号 这个 终于被用了起来，哈哈'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Document'
, @level2type = 'COLUMN', @level2name = N'SysSerialNumber'
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'Document', 
'COLUMN', N'DepartmentID')) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'发文单位'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Document'
, @level2type = 'COLUMN', @level2name = N'DepartmentID'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'发文单位'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Document'
, @level2type = 'COLUMN', @level2name = N'DepartmentID'
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'Document', 
'COLUMN', N'DocType')) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'这个字段一共有五类，表示五种不同的操作'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Document'
, @level2type = 'COLUMN', @level2name = N'DocType'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'这个字段一共有五类，表示五种不同的操作'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Document'
, @level2type = 'COLUMN', @level2name = N'DocType'
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'Document', 
'COLUMN', N'Code')) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'这个字段也不再用了，作古'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Document'
, @level2type = 'COLUMN', @level2name = N'Code'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'这个字段也不再用了，作古'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Document'
, @level2type = 'COLUMN', @level2name = N'Code'
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'Document', 
'COLUMN', N'HandleUserID')) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'持有人，貌似这个字段要作古了，这个字段不再用了'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Document'
, @level2type = 'COLUMN', @level2name = N'HandleUserID'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'持有人，貌似这个字段要作古了，这个字段不再用了'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Document'
, @level2type = 'COLUMN', @level2name = N'HandleUserID'
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'Document', 
'COLUMN', N'Portion')) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'份数'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Document'
, @level2type = 'COLUMN', @level2name = N'Portion'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'份数'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Document'
, @level2type = 'COLUMN', @level2name = N'Portion'
GO

-- ----------------------------
-- Table structure for Position
-- ----------------------------
DROP TABLE [dbo].[Position]
GO
CREATE TABLE [dbo].[Position] (
[ID] int NOT NULL IDENTITY(1,1) ,
[Name] varchar(100) NOT NULL ,
[Remark] varchar(100) NULL 
)


GO
DBCC CHECKIDENT(N'[dbo].[Position]', RESEED, 6)
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'Position', 
NULL, NULL)) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'岗位'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Position'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'岗位'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Position'
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'Position', 
'COLUMN', N'ID')) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'ID'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Position'
, @level2type = 'COLUMN', @level2name = N'ID'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'ID'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Position'
, @level2type = 'COLUMN', @level2name = N'ID'
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'Position', 
'COLUMN', N'Name')) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'岗位名称'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Position'
, @level2type = 'COLUMN', @level2name = N'Name'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'岗位名称'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Position'
, @level2type = 'COLUMN', @level2name = N'Name'
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'Position', 
'COLUMN', N'Remark')) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'备注'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Position'
, @level2type = 'COLUMN', @level2name = N'Remark'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'备注'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'Position'
, @level2type = 'COLUMN', @level2name = N'Remark'
GO

-- ----------------------------
-- Table structure for PositionAothority
-- ----------------------------
DROP TABLE [dbo].[PositionAothority]
GO
CREATE TABLE [dbo].[PositionAothority] (
[ID] int NOT NULL IDENTITY(1,1) ,
[PositionID] int NULL ,
[AuthorityID] int NULL 
)


GO
DBCC CHECKIDENT(N'[dbo].[PositionAothority]', RESEED, 68)
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'PositionAothority', 
NULL, NULL)) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'岗位权限'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'PositionAothority'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'岗位权限'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'PositionAothority'
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'PositionAothority', 
'COLUMN', N'ID')) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'ID'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'PositionAothority'
, @level2type = 'COLUMN', @level2name = N'ID'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'ID'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'PositionAothority'
, @level2type = 'COLUMN', @level2name = N'ID'
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'PositionAothority', 
'COLUMN', N'PositionID')) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'岗位ID'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'PositionAothority'
, @level2type = 'COLUMN', @level2name = N'PositionID'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'岗位ID'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'PositionAothority'
, @level2type = 'COLUMN', @level2name = N'PositionID'
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'PositionAothority', 
'COLUMN', N'AuthorityID')) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'权限ID'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'PositionAothority'
, @level2type = 'COLUMN', @level2name = N'AuthorityID'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'权限ID'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'PositionAothority'
, @level2type = 'COLUMN', @level2name = N'AuthorityID'
GO

-- ----------------------------
-- Table structure for User
-- ----------------------------
DROP TABLE [dbo].[User]
GO
CREATE TABLE [dbo].[User] (
[ID] int NOT NULL IDENTITY(1,1) ,
[Username] varchar(100) NOT NULL ,
[Password] varchar(100) NOT NULL ,
[DepartmentID] int NULL ,
[PositionID] int NULL ,
[Remark] varchar(100) NULL ,
[Name] varchar(100) NOT NULL 
)


GO
DBCC CHECKIDENT(N'[dbo].[User]', RESEED, 27)
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'User', 
NULL, NULL)) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'用户'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'User'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'用户'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'User'
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'User', 
'COLUMN', N'ID')) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'ID'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'User'
, @level2type = 'COLUMN', @level2name = N'ID'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'ID'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'User'
, @level2type = 'COLUMN', @level2name = N'ID'
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'User', 
'COLUMN', N'Username')) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'用户名'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'User'
, @level2type = 'COLUMN', @level2name = N'Username'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'用户名'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'User'
, @level2type = 'COLUMN', @level2name = N'Username'
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'User', 
'COLUMN', N'Password')) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'密码'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'User'
, @level2type = 'COLUMN', @level2name = N'Password'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'密码'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'User'
, @level2type = 'COLUMN', @level2name = N'Password'
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'User', 
'COLUMN', N'DepartmentID')) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'部门id'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'User'
, @level2type = 'COLUMN', @level2name = N'DepartmentID'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'部门id'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'User'
, @level2type = 'COLUMN', @level2name = N'DepartmentID'
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'User', 
'COLUMN', N'PositionID')) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'岗位id'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'User'
, @level2type = 'COLUMN', @level2name = N'PositionID'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'岗位id'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'User'
, @level2type = 'COLUMN', @level2name = N'PositionID'
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'User', 
'COLUMN', N'Remark')) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'备注'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'User'
, @level2type = 'COLUMN', @level2name = N'Remark'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'备注'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'User'
, @level2type = 'COLUMN', @level2name = N'Remark'
GO
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 
'SCHEMA', N'dbo', 
'TABLE', N'User', 
'COLUMN', N'Name')) > 0) 
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'姓名'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'User'
, @level2type = 'COLUMN', @level2name = N'Name'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'姓名'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'User'
, @level2type = 'COLUMN', @level2name = N'Name'
GO

-- ----------------------------
-- Procedure structure for paging
-- ----------------------------
DROP PROCEDURE [dbo].[paging]
GO

CREATE PROCEDURE [dbo].[paging]
    @Table VARCHAR(1000), --表名,多表是请使用 tA a inner join tB b On a.AID = b.AID
    @TIndex NVARCHAR(100),    --主键 
    @Column NVARCHAR(2000) = '*',--要查询的字段，全部字段就为*
    @Sql NVARCHAR(3000) = '',--Where条件
    @PageIndex INT = 1,    --开始页码
    @PageSize INT = 10,        --每页查询数据的行数
    @Sort NVARCHAR(200) = '' --排序的字段

AS

 

DECLARE @strWhere VARCHAR(2000)
DECLARE @strsql NVARCHAR(3900)
IF @Sql IS NOT NULL AND len(LTRIM(RTRIM(@Sql)))>0
  BEGIN
   SET @strWhere = ' WHERE ' + @Sql + ' '
  END
ELSE
  BEGIN
   SET @strWhere = ''
  END
        
IF (charindex(LTRIM(RTRIM(@TIndex)),@Sort)=0)
BEGIN
    IF(@Sort='')
        SET @Sort = @TIndex + ' DESC '
    ELSE
        SET @Sort = @Sort+ ' , '+@TIndex + ' DESC '
END
IF @PageIndex < 1
  SET @PageIndex = 1

        IF @PageIndex = 1  
        BEGIN 
          SET @strsql = 'SELECT TOP ' + str(@PageSize) +' '+@Column+ '  FROM ' + @Table + ' ' + @strWhere + ' ORDER BY  '+ @Sort
        END 
        ELSE
          BEGIN
         
            DECLARE @START_ID NVARCHAR(50)
            DECLARE @END_ID NVARCHAR(50)
            SET @START_ID = convert(NVARCHAR(50),(@PageIndex - 1) * @PageSize + 1)
            SET @END_ID = convert(NVARCHAR(50),@PageIndex * @PageSize)
            SET @strsql =  ' SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY '+@Sort+') AS RowNum, 
             '+@Column+ '
              FROM '+@Table   + @strWhere +') AS D
           WHERE RowNum BETWEEN '+@START_ID+' AND ' +@END_ID +' ORDER BY ID asc' --+@Sort
          END
EXEC(@strsql)
PRINT @strsql
    SET @strsql = 'SELECT  Count(1) as TotalRecords FROM ' + @Table   + @strWhere  
PRINT @strsql
EXEC(@strsql)

GO

-- ----------------------------
-- Procedure structure for paging_bak
-- ----------------------------
DROP PROCEDURE [dbo].[paging_bak]
GO

CREATE PROCEDURE [dbo].[paging_bak]
    @Table VARCHAR(1000), --表名,多表是请使用 tA a inner join tB b On a.AID = b.AID
    @TIndex NVARCHAR(100),    --主键 
    @Column NVARCHAR(2000) = '*',--要查询的字段，全部字段就为*
    @Sql NVARCHAR(3000) = '',--Where条件
    @PageIndex INT = 1,    --开始页码
    @PageSize INT = 10,        --每页查询数据的行数
    @Sort NVARCHAR(200) = '' --排序的字段

AS

 

DECLARE @strWhere VARCHAR(2000)
DECLARE @strsql NVARCHAR(3900)
IF @Sql IS NOT NULL AND len(LTRIM(RTRIM(@Sql)))>0
  BEGIN
   SET @strWhere = ' WHERE ' + @Sql + ' '
  END
ELSE
  BEGIN
   SET @strWhere = ''
  END
        
IF (charindex(LTRIM(RTRIM(@TIndex)),@Sort)=0)
BEGIN
    IF(@Sort='')
        SET @Sort = @TIndex + ' ASC '
    ELSE
        SET @Sort = @Sort+ ' , '+@TIndex + ' DESC '
END
IF @PageIndex < 1
  SET @PageIndex = 1

        IF @PageIndex = 1  
        BEGIN 
          SET @strsql = 'SELECT TOP ' + str(@PageSize) +' '+@Column+ '  FROM ' + @Table + ' ' + @strWhere + ' ORDER BY  '+ @Sort
        END 
        ELSE
          BEGIN
         
            DECLARE @START_ID NVARCHAR(50)
            DECLARE @END_ID NVARCHAR(50)
            SET @START_ID = convert(NVARCHAR(50),(@PageIndex - 1) * @PageSize + 1)
            SET @END_ID = convert(NVARCHAR(50),@PageIndex * @PageSize)
            SET @strsql =  ' SELECT '
						+@Column+ 
						'FROM (SELECT ROW_NUMBER() OVER(ORDER BY '+@Sort+') AS RowNum, '
							+@Column+ '
              FROM '+@Table +' WITH(NOLOCK) ' + @strWhere +') AS D
           WHERE RowNum BETWEEN '+@START_ID+' AND ' +@END_ID +' ORDER BY '+@Sort
          END
EXEC(@strsql)
PRINT @strsql
    --SET @strsql = 'SELECT  Count(1) as TotalRecords FROM ' + @Table +' WITH(NOLOCK) ' + @strWhere  
		SET @strsql = 'SELECT  Count(1) as TotalRecords FROM ' + @Table   + @strWhere  
PRINT @strsql
EXEC(@strsql)

GO

-- ----------------------------
-- Indexes structure for table Authority
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table Authority
-- ----------------------------
ALTER TABLE [dbo].[Authority] ADD PRIMARY KEY ([ID])
GO

-- ----------------------------
-- Indexes structure for table Department
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table Department
-- ----------------------------
ALTER TABLE [dbo].[Department] ADD PRIMARY KEY ([ID])
GO

-- ----------------------------
-- Indexes structure for table Directory
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table Directory
-- ----------------------------
ALTER TABLE [dbo].[Directory] ADD PRIMARY KEY ([ID])
GO

-- ----------------------------
-- Indexes structure for table DocConcerned
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table DocConcerned
-- ----------------------------
ALTER TABLE [dbo].[DocConcerned] ADD PRIMARY KEY ([ID])
GO

-- ----------------------------
-- Indexes structure for table DocHandler
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table DocHandler
-- ----------------------------
ALTER TABLE [dbo].[DocHandler] ADD PRIMARY KEY ([ID])
GO

-- ----------------------------
-- Uniques structure for table DocHandler
-- ----------------------------
ALTER TABLE [dbo].[DocHandler] ADD UNIQUE ([Code] ASC)
GO

-- ----------------------------
-- Indexes structure for table DocReadLog
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table DocReadLog
-- ----------------------------
ALTER TABLE [dbo].[DocReadLog] ADD PRIMARY KEY ([ID])
GO

-- ----------------------------
-- Indexes structure for table Document
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table Document
-- ----------------------------
ALTER TABLE [dbo].[Document] ADD PRIMARY KEY ([ID])
GO

-- ----------------------------
-- Uniques structure for table Document
-- ----------------------------
ALTER TABLE [dbo].[Document] ADD UNIQUE ([Code] ASC)
GO

-- ----------------------------
-- Indexes structure for table Position
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table Position
-- ----------------------------
ALTER TABLE [dbo].[Position] ADD PRIMARY KEY ([ID])
GO

-- ----------------------------
-- Indexes structure for table PositionAothority
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table PositionAothority
-- ----------------------------
ALTER TABLE [dbo].[PositionAothority] ADD PRIMARY KEY ([ID])
GO

-- ----------------------------
-- Indexes structure for table User
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table User
-- ----------------------------
ALTER TABLE [dbo].[User] ADD PRIMARY KEY ([ID])
GO

-- ----------------------------
-- Uniques structure for table User
-- ----------------------------
ALTER TABLE [dbo].[User] ADD UNIQUE ([Username] ASC)
GO

-- ----------------------------
-- Foreign Key structure for table [dbo].[DocConcerned]
-- ----------------------------
ALTER TABLE [dbo].[DocConcerned] ADD FOREIGN KEY ([DocID]) REFERENCES [dbo].[Document] ([ID]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [dbo].[DocConcerned] ADD FOREIGN KEY ([UserID]) REFERENCES [dbo].[User] ([ID]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [dbo].[DocHandler]
-- ----------------------------
ALTER TABLE [dbo].[DocHandler] ADD FOREIGN KEY ([DocID]) REFERENCES [dbo].[Document] ([ID]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [dbo].[DocHandler] ADD FOREIGN KEY ([HandlerUserID]) REFERENCES [dbo].[User] ([ID]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [dbo].[DocReadLog]
-- ----------------------------
ALTER TABLE [dbo].[DocReadLog] ADD FOREIGN KEY ([DocumentID]) REFERENCES [dbo].[Document] ([ID]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [dbo].[DocReadLog] ADD FOREIGN KEY ([LeaderId]) REFERENCES [dbo].[User] ([ID]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [dbo].[DocReadLog] ADD FOREIGN KEY ([ReadUserID]) REFERENCES [dbo].[User] ([ID]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [dbo].[Document]
-- ----------------------------
ALTER TABLE [dbo].[Document] ADD FOREIGN KEY ([DepartmentID]) REFERENCES [dbo].[Department] ([ID]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [dbo].[Document] ADD FOREIGN KEY ([DocClassID]) REFERENCES [dbo].[Directory] ([ID]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [dbo].[Document] ADD FOREIGN KEY ([UserID]) REFERENCES [dbo].[User] ([ID]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [dbo].[PositionAothority]
-- ----------------------------
ALTER TABLE [dbo].[PositionAothority] ADD FOREIGN KEY ([AuthorityID]) REFERENCES [dbo].[Authority] ([ID]) ON DELETE CASCADE ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[PositionAothority] ADD FOREIGN KEY ([PositionID]) REFERENCES [dbo].[Position] ([ID]) ON DELETE CASCADE ON UPDATE CASCADE
GO

-- ----------------------------
-- Foreign Key structure for table [dbo].[User]
-- ----------------------------
ALTER TABLE [dbo].[User] ADD FOREIGN KEY ([DepartmentID]) REFERENCES [dbo].[Department] ([ID]) ON DELETE CASCADE ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[User] ADD FOREIGN KEY ([PositionID]) REFERENCES [dbo].[Position] ([ID]) ON DELETE CASCADE ON UPDATE CASCADE
GO
